﻿using System.Data;
using System.Diagnostics;
using System.Reflection;
using System.Text;
using KalevaAalto.TypePart;
using Microsoft.Extensions.ObjectPool;
using Microsoft.Extensions.Primitives;
using NetTaste;
using SqlSugar;

namespace KalevaAalto.DataPart.Base
{
    public abstract class DataBaseBase
    {
        private DefaultObjectPool<ISqlSugarClient> _pool;

        public DataBaseBase()
        {
            var poolPolicy = new SqlSugarClientPooledObjectPolicy(GetSqlSugarClient);
            _pool = new DefaultObjectPool<ISqlSugarClient>(poolPolicy);
        }

        public abstract SqlSugarClient GetSqlSugarClient();





        public ISqlSugarClient db => _pool.Get();

        public abstract void Connect();
        public abstract Task ConnectAsync();

        public abstract bool Status { get; }



        #region Run

        public virtual void Run(string sql)
        {
            try
            {
                db.Ado.ExecuteCommand(sql);
            }
            catch (Exception error)
            {
                throw new Exception(error.Message + '|' + sql);
            }
        }
        public virtual async Task RunAsync(string sql, CancellationToken token = default)
        {
            try
            {
                await db.Ado.ExecuteCommandAsync(sql, null, token);
            }
            catch (Exception error)
            {
                throw new Exception(error.Message + '|' + sql);
            }
        }

        #endregion


        #region Query
        public virtual DataTable Query(string sql)
        {
            try
            {
                return db.Ado.GetDataTable(sql);
            }
            catch (Exception error)
            {
                throw new Exception(error.Message + '|' + sql);
            }
        }
        public async virtual Task<DataTable> QueryAsync(string sql, CancellationToken token = default)
        {
            try
            {
                return await db.Ado.GetDataTableAsync(sql);
            }
            catch (Exception error)
            {
                throw new Exception(error.Message + '|' + sql);
            }

        }

        #endregion


        #region GetEntitiy Infos

        public virtual string GetEntityTableName(Type type)
        {
            var info = type.GetCustomAttribute<SugarTable>();
            if (info is not null && !string.IsNullOrEmpty(info.TableName)) return info.TableName;
            else return type.Name;
        }

        public virtual string GetEntityTableName<T>()
        {
            return GetEntityTableName(typeof(T)); 
        }
            

        public virtual string[] GetEntityColumnNames(Type type)
        {
            var propertities = type.GetProperties();
            var sugarColumns = propertities
                .Select(p => p.GetCustomAttribute<SugarColumn>())
                .Where(it => it is not null && !it.IsIgnore)
                .ToArray();
            var columnNames = sugarColumns
                .Select(it => it!.ColumnName)
                .ToArray();

            return columnNames;
        }
            

        public virtual string[] GetEntityColumnNames<T>()
        {
            return GetEntityColumnNames(typeof(T));
        }

        #endregion


        #region Get DataBase Informations
        public virtual string[] GetDataBaseTableNames()
        {
            DataTable dataTable = Query("SHOW FULL TABLES WHERE table_type = 'BASE TABLE';");
            return dataTable.Rows.Cast<DataRow>().Select(it => (string)it[0]).ToArray();
        }

        public virtual async Task<string[]> GetDataBaseTableNamesAsync(CancellationToken token = default)
        {
            DataTable dataTable = await QueryAsync("SHOW FULL TABLES WHERE table_type = 'BASE TABLE';", token);
            return dataTable.Rows.Cast<DataRow>().Select(it => (string)it[0]).ToArray();
        }


        public virtual string[] GetDataBaseColumnNames(string tableName)
        {
            DataTable dataTable = Query($"DESCRIBE `{tableName}`;");
            return dataTable.Rows.Cast<DataRow>().Select(it => (string)it[@"Field"]).ToArray();
        }

        public virtual string[] GetDataBaseColumnNames(Type type)
            => GetDataBaseColumnNames(GetEntityTableName(type));

        public virtual string[] GetDataBaseColumnNames<T>()
            => GetDataBaseColumnNames(GetEntityTableName<T>());

        


        public virtual async Task<string[]> GetDataBaseColumnNamesAsync(string tableName, CancellationToken token = default)
        {
            DataTable dataTable = await QueryAsync($"DESCRIBE `{tableName}`;", token);
            return dataTable.Rows.Cast<DataRow>().Select(it => (string)it[@"Field"]).ToArray();
        }

        public virtual Task<string[]> GetDataBaseColumnNamesAsync(Type type, CancellationToken token = default)
            => GetDataBaseColumnNamesAsync(GetEntityTableName(type));

        public virtual Task<string[]> GetDataBaseColumnNamesAsync<T>(CancellationToken token = default)
            => GetDataBaseColumnNamesAsync(GetEntityTableName<T>());

        public virtual bool DataBaseTableExists(string tableName)
            => GetDataBaseTableNames().Any(it => it == tableName);

        public virtual async Task<bool> DataBaseTableExistsAsync(string tableName, CancellationToken token = default)
            => (await GetDataBaseTableNamesAsync()).Any(it => it == tableName);


        public virtual bool DataBaseTableExists<T>()
            => GetDataBaseTableNames().Any(it => it == GetTableName<T>());

        public virtual async Task<bool> DataBaseTableExistsAsync<T>( CancellationToken token = default)
            => (await GetDataBaseTableNamesAsync()).Any(it => it == GetTableName<T>());

        #endregion



        #region Check Entity And DataBase

        public virtual string CheckClass(Type type, IEnumerable<string>? extraEntityColumnNames = null)
        {
            IEnumerable<string> databaseColumnNames = GetDataBaseColumnNames(type);
            IEnumerable<string> entityColumnNames = GetEntityColumnNames(type).Union(extraEntityColumnNames ?? Enumerable.Empty<string>()).ToArray();

            string str = GetCompareColumnsString(@"数据库", @"实体", databaseColumnNames, entityColumnNames);
            if (string.IsNullOrEmpty(str))
            {
                return string.Empty;
            }
            else
            {
                return GetTableName(type) + '：' + str;
            }

        }

        public string CheckClass<T>(IEnumerable<string>? extraColumnNames = null)
        {
            return CheckClass(typeof(T), extraColumnNames);
        }

        




        public virtual async Task<string> CheckClassAsync(Type type, IEnumerable<string>? extraEntityColumnNames = null, CancellationToken token = default)
        {
            IEnumerable<string> databaseColumnNames = await GetDataBaseColumnNamesAsync(type,token);
            IEnumerable<string> entityColumnNames = GetEntityColumnNames(type).Union(extraEntityColumnNames ?? Enumerable.Empty<string>()).ToArray();

            return GetCompareColumnsString(@"数据库", @"实体", databaseColumnNames, entityColumnNames);
        }

        public Task<string> CheckClassAsync<T>(IEnumerable<string>? extraColumnNames = null, CancellationToken token = default)
             =>CheckClassAsync(typeof(T), extraColumnNames,token);

        #endregion

        #region Insert
        private string GetInsertSql(DataTable dataTable)
        {
            string tableName = dataTable.TableName;
            if (string.IsNullOrEmpty(tableName) || dataTable.Columns.Count <= 0 || dataTable.Rows.Count <= 0) return string.Empty;


            StringBuilder columnNamesSqlString = new StringBuilder();
            foreach (string columnName in dataTable.Columns.Cast<DataColumn>().Select(it => it.ColumnName))
            {
                columnNamesSqlString.Append($"`{columnName}`");
                columnNamesSqlString.Append(',');
            }
            columnNamesSqlString.Remove(columnNamesSqlString.Length - 1, 1);

            StringBuilder rowsSqlString = new StringBuilder();
            foreach (DataRow dataRow in dataTable.Rows)
            {
                StringBuilder rowSqlString = new StringBuilder();
                foreach (object? value in dataRow.ItemArray)
                {
                    if (value is null || value is DBNull)
                    {
                        rowSqlString.Append(@"DEFAULT");
                    }
                    else if(value is bool)
                    {
                        bool boolEntity = (bool)value;
                        rowSqlString.Append(boolEntity ? @"TRUE" : @"FALSE");
                    }
                    else
                    {
                        rowSqlString.Append($"'{value}'");
                    }
                    rowSqlString.Append(',');
                }
                rowSqlString.Remove(rowSqlString.Length - 1, 1);


                rowsSqlString.Append($"({rowSqlString})");
                rowsSqlString.Append(',');
            }
            rowsSqlString.Remove(rowsSqlString.Length - 1, 1);

            return $"INSERT INTO `{dataTable.TableName}`({columnNamesSqlString}) VALUE{rowsSqlString};";
        }


        private string GetInsertSql<T>(IEnumerable<T> objEnumerable)
        {
            string tableName = GetTableName<T>();
            IDictionary<string,PropertyInfo> columnInfoDic = GetColumnInfoDic<T>(false,false);
            T[] objs = objEnumerable.ToArray();
            if (string.IsNullOrEmpty(tableName) 
                || objs.Length <= 0 
                || columnInfoDic.Count<=0)
            {
                return string.Empty;
            }


            StringBuilder columnNamesSqlString = new StringBuilder();
            foreach (string columnName in columnInfoDic.Keys)
            {
                columnNamesSqlString.Append($"`{columnName}`");
                columnNamesSqlString.Append(',');
            }
            columnNamesSqlString.Remove(columnNamesSqlString.Length - 1, 1);

            StringBuilder rowsSqlString = new StringBuilder();
            foreach (T obj in objs)
            {
                


                StringBuilder rowSqlString = new StringBuilder();
                foreach (var property in columnInfoDic.Values)
                {
                    object? value = property.GetValue(obj);
                    if (value is null)
                    {
                        rowSqlString.Append(@"DEFAULT");
                    }
                    else if (value is bool)
                    {
                        bool boolEntity = (bool)value;
                        rowSqlString.Append(boolEntity ? @"TRUE" : @"FALSE");
                    }
                    else if(value is Enum)
                    {
                        long longEntity = System.Convert.ToInt64(value); ;
                        rowSqlString.Append(longEntity);
                    }
                    else if (value.GetType().IsOrNullableNumber())
                    {
                        rowSqlString.Append(value);
                    }
                    else
                    {
                        rowSqlString.Append($"'{value}'");
                    }
                    rowSqlString.Append(',');
                }
                rowSqlString.Remove(rowSqlString.Length - 1, 1);


                rowsSqlString.Append($"({rowSqlString})");
                rowsSqlString.Append(',');
            }
            rowsSqlString.Remove(rowsSqlString.Length - 1, 1);

            


            return $"INSERT INTO `{tableName}`({columnNamesSqlString}) VALUE{rowsSqlString};";
        }


        public virtual void Insert(DataTable dataTable)
        {
            string sql = GetInsertSql(dataTable);
            Run(sql);
        }
        

        public virtual async Task InsertAsync(DataTable dataTable, CancellationToken token = default)
        {
            string sql = GetInsertSql(dataTable);
            await RunAsync(sql, token);
        }




        public int s_partCount = 100;
        public virtual void Insert<T>(IEnumerable<T> objs)
        {
            var objArray = objs.ToArray();
            for (int i = 0; i < objArray.Length; i += s_partCount)
            {
                var subset = objArray.Skip(i).Take(s_partCount).ToArray();
                string sql = GetInsertSql(subset);
                Run(sql);
            }
        }

        public virtual async Task InsertAsync<T>(IEnumerable<T> objs, CancellationToken token = default)
        {
            var objArray = objs.ToArray();
            for (int i = 0; i < objArray.Length; i += s_partCount)
            {
                var subset = objArray.Skip(i).Take(s_partCount).ToArray();
                string sql = GetInsertSql(subset);
                await RunAsync(sql, token);
            }
        }

        #endregion

        #region Delete
        private static string GetConditionsSqlString(IEnumerable<string> conditionIEnumerable)
        {
            string[] conditions = conditionIEnumerable.ToArray();
            if (conditions.Length > 0)
            {
                StringBuilder conditionsSqlString = new StringBuilder();
                string dividPart = @" AND ";
                foreach (string condition in conditions)
                {
                    conditionsSqlString.Append($"({condition})");
                    conditionsSqlString.Append(dividPart);
                }
                conditionsSqlString.Remove(conditionsSqlString.Length - dividPart.Length, dividPart.Length);
                return conditionsSqlString.ToString();
            }
            else return @"1";


        }

        private static string GetDeleteString(string tableName, IEnumerable<string>? conditionIEnumerable = null)
        {
            string[]? conditions = conditionIEnumerable?.ToArray() ?? null;
            if (conditions is not null && conditions.Length > 0)
            {
                return $"DELETE FROM `{tableName}` WHERE {GetConditionsSqlString(conditions)};";
            }
            else
            {
                return $"DELETE FROM `{tableName}`;";
            }
        }

        public virtual void Delete(string tableName, IEnumerable<string>? conditions = null)
        {
            string sql = GetDeleteString(tableName, conditions);
            Run(sql);
        }
        

        public virtual async Task DeleteAsync(string tableName, IEnumerable<string>? conditions = null, CancellationToken token = default)
        {
            string sql = GetDeleteString(tableName, conditions);
            await RunAsync(sql, token);
        }
        

        #endregion












    }
}
